<?php
/**
 * 数据备份功能
 * User: cory
 * Date: 2017/2/21
 * Time: 9:40
 */
namespace Home\Service;


/**
 * 数据库Service
 */
class DatabaseService extends PSIBaseService {
    private $dir_back_path = 'Public/sql'; //数据库存储的目录

    /**
     * 获取备份数据列表
     */
    public function sql_list(){
        $dir_list = $this->dir_list($this->dir_back_path);

        $database_list = array();
        foreach ($dir_list as $dir_k => $dir){
            $database_list[$dir_k]['data_path'] = $dir;
            $database_list[$dir_k]['data_time'] = date('Y-m-d H:i:s',basename($dir,'.sql'));
            $fstat = fstat(fopen($dir,"r"));
            $database_list[$dir_k]['data_size'] = round($fstat["size"]/1024,2).'kb';
        }
        rsort($database_list);
        return $database_list;
    }

    /**
     * 数组备份   t_config表出错了,弟三和第四个字段，明天记得检查一下！！！！！！！！！！！！！！！！！！！！！
     */
    public function back(){
        $table=$this->getTable();
        $struct=$this->bakStruct($table);
        $record=$this->bakRecord($table);
        $sqls=$struct.$record;
        $dir=$this->dir_back_path."/".time().".sql";
        file_put_contents($dir,$sqls,FILE_APPEND | LOCK_EX);
        if(file_exists($dir))
        {
            return array(
                'info'=>"备份成功",
                'status'=>1
            );
        }else
        {
            return array(
                'info'=>"备份失败",
                'status'=>0
            );
        }
    }

    /**
     * 数据删除
     */
    public function del($path){
        if(file_exists($path)){
            unlink($path);
            return array(
                'info'=>"删除成功!",
                'status'=>1
            );
        }else{
            return array(
                'info'=>"文件不存在",
                'status'=>0
            );
        }
    }
    /**
     * 数据还原
     */
    public function restore($filenames){

//        $filenames = 'Public/sql/1487730073.sql';
        set_time_limit(600);
        //将文件的内容取出，放进$data字符串
        $data = file_get_contents($filenames);
        //将数据里面的\r换行符替换成回车符
        $data = str_replace("\r", "\n", $data);
        //下面两行解释:sql数据里边每条执行的语句都会有;<--分号,然后上面已经将换行符换成\n
        //所以;分号后面有\n，匹配出来就可以将备份文件拆成每条sql语句
        $regular = "/;\n/";
        $data = preg_split($regular,trim($data));
        //删除注释
        $sign = 0; //数组索引标志
        foreach($data as $data_row){
            //取出每行数据根据\n分割成数组
            $data_arr = explode("\n", $data_row);
            //分割成数组后，判断每一个元素，是否是--开头的，或者是空字符串，都去掉
            foreach ($data_arr as $_k=>$_v){
                if(strlen($_v) == 0 || preg_match("/--/",$_v) == 1){
                    //如果该元素等于空字符串或者匹配--,有的话也去掉
                    unset($data_arr[$_k]);
                }
            }
            //替换完$data_arr后将数据拼成字符串
            $data[$sign] = implode("", $data_arr);
            $sign++;
        }
        $obj = M();
        $sql = '';
        $i = 0;
        foreach($data as $key=>$val){
            //每一千条执行一次
            if($i == 1000){
                $obj->execute($sql); //执行每条sql语句
                $i=0;
            }else{
                $sql.=$val.';';
                $i++;
            }
        }

        return array(
            'info'=>"还原成功!",
            'status'=>1
        );
    }

    /**
     * 判断路径字符串后面是否带/或者\\,或者没有带，然后在字符串后面替换/
     * @path str 路径
     * @return str 返回路径
     */
    protected function dir_path($path) {
        $path = str_replace('\\', '/', $path);
        if (substr($path, -1) != '/') $path = $path . '/';
        return $path;
    }
    /**
     * 列出目录下的所有文件
     *
     * @param str $path 目录
     * @param str $exts 后缀
     * @param array $list 路径数组
     * @return array 返回路径数组
     */
    protected function dir_list($path, $exts = '', $list = array()) {
        $path = $this->dir_path($path);
        $files = glob($path . '*');
        foreach($files as $v) {
            if (!$exts || preg_match("/\.($exts)/i", $v)) {
                $list[] = $v;
                if (is_dir($v)) {
                    $list = dir_list($v, $exts, $list);
                }
            }
        }
        return $list;
    }

    /**
     * 拿出所有的表
     * @return array
     */
    protected function getTable(){
        $dbName=C('DB_NAME');
        $result=M()->query('show tables from '.$dbName);
        foreach ($result as $v){
            $tbArray[]=$v['tables_in_'.C('DB_NAME')];
        }
        return $tbArray;
    }


    /**
     * 拿出所有的表结构
     * @param $array 表数组
     * @return mixed
     */
    protected function bakStruct($array){
        $sql = '';
        foreach ($array as $v){
            $tbName=$v;
            $result=M()->query('show columns from '.$tbName);
            $sql.="--\r\n";
            $sql.="-- 数据表结构: `$tbName`\r\n";
            $sql.="--\r\n\r\n";
            $sql.="DROP TABLE IF EXISTS `$tbName`;\r\n";
            $sql.="create table `$tbName` (\r\n";
            $rsCount=count($result);
            foreach ($result as $k=>$v){
                $field  =       $v['field'];
                $type   =       $v['type'];
                $default=       $v['default'];
                $extra  =       $v['extra'];
                $null   =       $v['null'];
                if(!($default=='')){
                    $default='default '.$default;
                }
                if($null=='NO'){
                    $null='not null';
                }else{
                    $null='null';
                }
                if($v['key']=='PRI'){
                    $key    =       'primary key';
                }else{
                    $key    =       '';
                }
                if($k<($rsCount-1)){
                    $sql.="`$field` $type $null $default $key $extra ,\r\n";
                }else{
                    //最后一条不需要","号
                    $sql.="`$field` $type $null $default $key $extra \r\n";
                }
            }
            $sql.=")engine=innodb charset=utf8;\r\n\r\n";
        }
        return str_replace(',)',')',$sql);
    }

    /**
     * 返回所有表数据
     * @param $array 表数组
     * @return mixed
     */
    protected function bakRecord($array){
        $sql = '';
        foreach ($array as $v){
            $tbName=$v;

            $rs=M()->query('select * from '.$tbName);
            $columns_rs=M()->query('show columns from '.$tbName);


            if(count($rs)<=0){
                continue;
            }
            $sql.="--\r\n";
            $sql.="-- 数据表中的数据: `$tbName`\r\n";
            $sql.="--\r\n\r\n";
            foreach ($rs as $k=>$vv){
                $sql.="INSERT INTO `$tbName` VALUES (";

                foreach ($vv as $key=>$value){
                    if($value==''){
                        foreach ($columns_rs as $col){
                            if($col['field'] == $key){
                                if( stristr ( $col['type'] ,  'int' ) !==  FALSE or  stristr ( $col['type'] ,  'decimal' ) !==  FALSE or  stristr ( $col['type'] ,  'date' ) !==  FALSE or  stristr ( $col['type'] ,  'time' ) !==  FALSE or  stristr ( $col['type'] ,  'year' ) !==  FALSE) {
                                    $value = 'null';
                                }else{
                                    $value="''";
                                }
                            }
                        }
                    }else{
                        $type=gettype($value);
                        if($type=='string'){
                            $value="'".addslashes($value)."'";
                        }
                    }

                    $sql.="$value," ;

                }
                $sql.=");\r\n\r\n";
            }

        }
        return str_replace(',)',')',$sql);

    }
}